/**
 * @author Pedro J Toro
 * csc8205 Advance Java - Assignment 02
 * This program creates an XML file by reading data from a database.
 */

import java.io.*;

// DOM classes.
import org.w3c.dom.DOMImplementation;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;

// JAXP
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.dom.DOMSource;

// SQL
import java.sql.*;

public class CreateXML {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws FileNotFoundException, ParserConfigurationException, ClassNotFoundException, TransformerConfigurationException, TransformerException {
        String fileName;
        if (args.length == 0) {
            fileName = "assg2.xml";
        } else {
            fileName = args[0];
        }
            
        PrintWriter out = new PrintWriter(new FileOutputStream(fileName));

        Document xmldoc = null;
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        DOMImplementation impl = builder.getDOMImplementation();
        Element e = null;

        // Document
        xmldoc = impl.createDocument(null, "Tickets", null);

        // Root element
        Element root = xmldoc.getDocumentElement();

        try {
            // load database driver class 
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            // connect to database 
            Connection conn = DriverManager.getConnection("jdbc:odbc:xmltest");
            // create statement to query database 
            Statement stm = conn.createStatement();

            // query database and capture the result returned 
            String SQL_query = "Select * from Assg2";
            ResultSet rst = stm.executeQuery(SQL_query);

            // process query results 
            while (rst.next()) {

                // Child
                e = xmldoc.createElementNS(null, "Ticket");
                e.appendChild(createElement(xmldoc, "TicketID", getString(rst.getString("TicketID"))));
                e.appendChild(createElement(xmldoc, "UserID", getString(rst.getString("UserID"))));
                e.appendChild(createElement(xmldoc, "Description", getString(rst.getString("Description"))));
                e.appendChild(createElement(xmldoc, "Solution", getString(rst.getString("Solution"))));
                e.appendChild(createElement(xmldoc, "Status", getString(rst.getString("Status"))));
                e.appendChild(createElement(xmldoc, "DateCreated", getString(rst.getString("DateCreated"))));
                e.appendChild(createElement(xmldoc, "DateClosed", getString(rst.getString("DateClosed"))));
                e.appendChild(createElement(xmldoc, "LastModified", getString(rst.getString("LastModified"))));

                root.appendChild(e);
            }

            // close connection 
            stm.close();
            conn.close();
        } catch (SQLException exp) {
            System.out.println(exp.getMessage());
        }

        // Serialisation through Tranform.
        DOMSource domSource = new DOMSource(xmldoc);
        StreamResult streamResult = new StreamResult(out);
        TransformerFactory tf = TransformerFactory.newInstance();
        Transformer serializer = tf.newTransformer();
        serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
        serializer.setOutputProperty(OutputKeys.DOCTYPE_SYSTEM, "assg2.dtd");
        serializer.setOutputProperty(OutputKeys.INDENT, "yes");
        serializer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4" );
        serializer.transform(domSource, streamResult);

    }

    /**
     * Makes sure null is not returned.
     * @param myString String to return
     * @return a String
     */
    private static String getString(String myString) {
        if (myString == null) {
            return "";
        } else {
            return myString;
        }
    }

    /**
     * Creates an Element with its text value.
     * @param xmldoc
     * @param elementName Name of element.
     * @param value Value of element.
     * @return Element with its text value to be used by DOM.
     */
    private static Element createElement(Document xmldoc, String elementName, String value) {
        Element e = null;
        Node n = null;

        e = xmldoc.createElementNS(null, elementName);
        n = xmldoc.createTextNode(value);
        e.appendChild(n);

        return e;
    }
}
